package vivek.project.pocketmoney.expenses.model
{
	import flash.data.*;
	import flash.errors.*;
	import flash.events.Event;
	import flash.net.LocalConnection;
	import flash.system.System;
	import mx.collections.ArrayCollection;
	import mx.controls.Alert;
	import mx.core.UIComponent;
	import mx.utils.ObjectUtil;
	import vivek.project.pocketmoney.admin.model.AdminConstants;
	import vivek.project.pocketmoney.admin.model.AdminDataCache;
	import vivek.project.pocketmoney.admin.model.AdminDataModel;
	import vivek.project.pocketmoney.admin.scripts.AdminSQLScript;
	import vivek.project.pocketmoney.common.CommonData;
	import vivek.project.pocketmoney.common.PocketmoneyDataInterface;
	import vivek.project.pocketmoney.common.events.CommonEvents;
	import vivek.project.pocketmoney.common.interfaces.*;
	import vivek.project.pocketmoney.common.logger.PMLogger;
	import vivek.project.pocketmoney.common.utils.ArrayUtils;
	import vivek.project.pocketmoney.common.utils.CommonConstants;
	import vivek.project.pocketmoney.expenses.events.ExpenseEvent;
	import vivek.project.pocketmoney.expenses.scripts.ExpenseSQLScript;

	/**
	 *
	 * @author M8070033
	 */
	public class ExpenseDataModel extends UIComponent implements PocketmoneyDataInterface
	{
		private static var MODULE:String="EXPENSES";
		//private  var _sqlConnection : SQLConnection = CommonData.getConnection();
		private static var instance:ExpenseDataModel;
		public static const monthStringValue:ArrayCollection=new ArrayCollection(["January", "Feburary", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"]);
		public static function getInstance():ExpenseDataModel
		{
			if (!instance)
			{
				/*instance = ExpenseDataModel(
				 Singleton.getInstance("vivek.project.pocketmoney.expenses.model::ExpenseDataModel"));*/
				instance=new ExpenseDataModel();
			}
			return instance;
		}
		public function ExpenseDataModel():void
		{
			loadData();
			adminDataModel.addEventListener("USERCHANGED", loadDataOnEvent);
			addEventListener("CoreDataChanged", loadDataOnEvent);
			addEventListener("ExpenseTransactionDeleted", refreshExpenseDataCache);
			addEventListener("ExpenseTransactionAdded", refreshExpenseDataCache);
			//InvestmentDataModel.getInstance().addEventListener("InvestmentChanged", refreshExpenseDataCache);
			addEventListener("ExpenseTransactionUpdated", refreshExpenseDataCache);
			addEventListener("BudgetTransactionAdded", refreshBudgetDataCache);
		}
		private var adminDataModel:AdminDataModel=AdminDataModel.getInstance();
		//private var expenseSummaryModel:ExpenseSummaryModel=ExpenseSummaryModel.getInstance();
		public function addCategory(obj:Object):int
		{
			try
			{
				var _statement:SQLStatement=new SQLStatement();
				var _sqlConnection:SQLConnection=CommonData.getConnection();
				//_sqlConnection.open(); 
				_statement.sqlConnection=_sqlConnection;
				_statement.text=ExpenseSQLScript.ADD_CATEGORY;
				_statement.parameters[":USERID"]=obj.userid;
				_statement.parameters[":cm_categoryname"]=obj.categoryname;
				_statement.parameters[":cm_categorydesc"]=obj.categorydesc;
				_statement.parameters[":cm_expensetype"]=obj.expensetype;
				_statement.parameters[":cm_mastercategory"]=obj.mastercategory;
				_statement.execute();
				refreshCategory(_sqlConnection);
				_sqlConnection.close();
				_statement=null;
			}
			catch (sqlError:SQLError)
			{
				//Alert.show(sqlError.details);
			}
			return 1;
		}
		public function addDependentExpenseRecord(obj:Object, randomId:Number, conn:SQLConnection):int
		{
			try
			{
				trace("Expense : addDependentTransferRecord : " + new Date().toTimeString() + " : step 2.5.1 done");
				var depositObj:Object=new Object();
				obj.istransferrecord=0;
				obj.exRate=adminDataModel.getCurrencyDetailsForAccount(obj.account).cu_rate;
				addObjToData(obj, randomId, conn);
				//refreshExpenseData(conn);
				dispatchEvent(new Event("ExpenseTransactionAdded"));
			}
			catch (sqlError:SQLError)
			{
				Alert.show(sqlError.details);
				return 1;
			}
			return 0;
		}
		//@Deprecated : we dont have to use this !!!!
		public function addDependentTransferRecord(obj:Object, randomId:Number, conn:SQLConnection, refresh:Boolean=true):int
		{
			try
			{
				trace("Expense : addDependentTransferRecord : " + new Date().toTimeString() + " : step 2.5.1 done");
				var depositObj:Object=new Object();
				var withdrawalObj:Object=new Object();
				obj.istransferrecord=1;
				depositObj=ObjectUtil.copy(obj);
				depositObj.type=0;
				depositObj.exRate=1.0; //adminDataModel.getCurrencyDetailsForAccount(depositObj.account).cu_rate;
				addObjToData(depositObj, randomId, conn);
				trace("Expense : addDependentTransferRecord : " + new Date().toTimeString() + " : step 2.5.2 done");
				withdrawalObj=ObjectUtil.copy(obj);
				withdrawalObj.type=1;
				withdrawalObj.account=withdrawalObj.transferAccount;
				withdrawalObj.userid=adminDataModel.getAccountForId(withdrawalObj.transferAccount).am_userid;
				withdrawalObj.exRate=adminDataModel.getCurrencyDetailsForAccount(withdrawalObj.transferAccount).cu_rate;
				addObjToData(withdrawalObj, randomId, conn);
				trace("Expense : addDependentTransferRecord : " + new Date().toTimeString() + " : step 2.5.3 done");
				//if (refresh)
				//refreshExpenseData(conn);
				trace("Expense : addDependentTransferRecord : " + new Date().toTimeString() + " : step 2.5.4 done");
				dispatchEvent(new Event("ExpenseTransactionAdded"));
			}
			catch (sqlError:SQLError)
			{
				Alert.show(sqlError.details);
				return 1;
			}
			return 0;
		}
		public function addExpenses(array:Array):int
		{
			try
			{
				var _sqlConnection:SQLConnection=CommonData.getConnection();
				_sqlConnection.begin();
				for each (var obj:Object in array)
				{
					var randomId:Number=new Number();
					randomId=Math.round(Math.random() * 1000000000);
					obj.exRate=adminDataModel.getCurrencyDetailsForAccount(obj.account).cu_rate;
					try
					{
						addObjToData(obj, randomId, _sqlConnection);
					}
					catch (error:Error)
					{
					}
				}
				_sqlConnection.commit();
				_sqlConnection.close();
				PMLogger.log("EXPENSE", "EXPENSE ADDED", obj);
				dispatchEvent(new Event("ExpenseTransactionAdded"));
			}
			catch (sqlError:SQLError)
			{
				Alert.show(sqlError.details);
			}
			finally
			{
			}
			return 1;
		}
		public function addExpense(obj:Object, _sqlConnection:SQLConnection=null):int
		{
			var createConnection:Boolean=false;
			try
			{
				if (_sqlConnection == null)
				{
					_sqlConnection=CommonData.getConnection();
					createConnection=true;
					_sqlConnection.begin();
				}
				var randomId:Number=new Number();
				randomId=Math.round(Math.random() * 1000000000);
				obj.exRate=adminDataModel.getCurrencyDetailsForAccount(obj.account).cu_rate;
				addObjToData(obj, randomId, _sqlConnection);
				if (createConnection)
				{
					_sqlConnection.commit();
					_sqlConnection.close();
				}
				dispatchEvent(new Event("ExpenseTransactionAdded"));
				PMLogger.log("EXPENSE", "EXPENSE ADDED", obj);
			}
			catch (sqlError:SQLError)
			{
				Alert.show(sqlError.details);
			}
			finally
			{
			}
			return 1;
		}
		public function addMasterCategory(obj:Object, _sqlConnection:SQLConnection):int
		{
			try
			{
				var _statement:SQLStatement=new SQLStatement();
				_statement.sqlConnection=_sqlConnection;
				_statement.text=ExpenseSQLScript.ADD_MASTER_CATEGORY;
				_statement.parameters[":mc_categoryname"]=obj.mc_categoryname;
				_statement.execute();
				refreshMasterCategory(_sqlConnection);
			}
			catch (sqlError:SQLError)
			{
				Alert.show(sqlError.details);
			}
			finally
			{
				_statement=null;
			}
			return 1;
		}
		public function addPayee(obj:Object, _sqlConnection:SQLConnection):int
		{
			try
			{
				var _statement:SQLStatement=new SQLStatement();
				_statement.sqlConnection=_sqlConnection;
				_statement.text=ExpenseSQLScript.ADD_PAYEE;
				_statement.parameters[":pm_payeeName"]=obj.payeeName;
				_statement.parameters[":USERID"]=AdminDataCache.ACCOUNT_IN_USE;
				_statement.execute();
				refreshPayee(_sqlConnection);
			}
			catch (sqlError:SQLError)
			{
				Alert.show(sqlError.details);
			}
			finally
			{
				_statement=null;
			}
			return 1;
		}
		public function addRecurssiveExpense(obj:Object, _sqlConnection:SQLConnection):void
		{
			var _statement:SQLStatement=new PMSqlStatement();
			try
			{
				_statement.sqlConnection=_sqlConnection;
				_statement.parameters[":rm_recursiondate"]=obj.onDate;
				_statement.parameters[":rm_amount"]=obj.amount;
				_statement.parameters[":rm_fromaccount"]=obj.account;
				_statement.parameters[":rm_toaccount"]=obj.toAccount;
				_statement.parameters[":rm_fromdate"]=obj.startDate;
				_statement.parameters[":rm_todate"]=obj.endDate;
				_statement.parameters[":rm_type"]=obj.type;
				_statement.parameters[":rm_category"]=obj.category;
				_statement.parameters[":rm_memo"]=obj.memo;
				_statement.parameters[":rm_status"]=obj.status;
				_statement.parameters[":rm_nextexecutiondate"]=RecurssionInterface.getNextExecutionDate(obj.startDate, obj.endDate, obj.onDate);
				_statement.text=ExpenseSQLScript.ADD_RECURSSIVE_EXPENSE;
				_statement.execute();
				refreshRecurringExpense(_sqlConnection);
			}
			catch (sqlError:SQLError)
			{
				trace(sqlError.details);
			}
		}
		public function addTransferRecord(obj:Object, _conn:SQLConnection=null):void
		{
			var conn:SQLConnection=new SQLConnection();
			if (_conn == null)
			{
				conn=CommonData.getConnection();
				conn.begin();
				_addTransferRecord(obj, conn);
				conn.commit();
				conn.close();
			}
			else
			{
				//conn == _conn;
				_addTransferRecord(obj, _conn);
			}
			//refreshExpenseData(conn);
			dispatchEvent(new Event("ExpenseTransactionAdded"));
		}
		public function deleteCategory(category:Object, _sqlConnection:SQLConnection):int
		{
			var _statement:SQLStatement=new PMSqlStatement();
			try
			{
				_statement.sqlConnection=_sqlConnection;
				_statement.text=ExpenseSQLScript.EXPENSE_CATEGORY;
				_statement.parameters[":categoryid"]=category.categoryid;
				_statement.execute();
				var data:Array=data=_statement.getResult().data;
				if (data == null)
				{
					_statement.clearParameters();
					_statement.text=ExpenseSQLScript.DELETE_CATEGORY_RECORD;
					_statement.parameters[":cm_categoryid"]=category.categoryid;
					_statement.execute();
					refreshCategory(_sqlConnection);
				}
				else
					return 9;
			}
			catch (sqlError:SQLError)
			{
				trace("Delete Cateogory : " + category.categoryid + ":" + sqlError.message + ":" + sqlError.details);
			}
			return 1;
		}
		public function deleteDependentExpense(expenseId:Number, refresh:Boolean, _sqlConnection:SQLConnection, obj:Object):void
		{
			var _statement:SQLStatement=new SQLStatement();
			try
			{
				_statement.sqlConnection=_sqlConnection;
				_statement.parameters[":expenseId"]=expenseId;
				_statement.text=ExpenseSQLScript.DELETE_EXPENSE_RECORD;
				_statement.execute();
				//if (refresh)
				//refreshExpenseData(_sqlConnection);
				//dispatchEvent(new Event("ExpenseTransactionDeleted"));
				PMLogger.log(MODULE, "EXPENSE DELETED", obj);
			}
			catch (sqlError:SQLError)
			{
				throw sqlError;
			}
		}
		public function deleteExpenses(expenses:Array):void
		{
			var connection:SQLConnection=CommonData.getConnection();
			var _statement:SQLStatement=new SQLStatement();
			var obj:Object=new Object();
			try
			{
				connection.begin();
				_statement.sqlConnection=connection;
				for each (obj in expenses)
				{
					_statement.parameters[":expenseId"]=obj.em_expenseid;
					_statement.text=ExpenseSQLScript.DELETE_EXPENSE_RECORD;
					_statement.execute();
					PMLogger.logMultiple("EXPENSE", "EXPENSE DELETED", obj);
				}
				connection.commit();
				connection.close();
			}
			catch (sqlError:SQLError)
			{
				connection.rollback();
				connection.close();
				Alert.show(sqlError.details);
			}
			switch (obj.em_category)
			{
				case CommonConstants.LOAN_CATEGORY_ID:
					dispatchEvent(new ExpenseEvent(CommonEvents.LOAN_EXPENSE_TRANSACTION_DELETED, obj, 1));
					break;
				case CommonConstants.PROPERTY_CATEGORY_ID:
					dispatchEvent(new ExpenseEvent(CommonEvents.PROPERTY_EXPENSE_TRANSACTION_DELETED, obj, 1));
					break;
			}
			dispatchEvent(new ExpenseEvent("ExpenseTransactionDeleted", obj, 1));
			PMLogger.flush();
		}
		public function deletePayee(obj:Object, _sqlConnection:SQLConnection):Boolean
		{
			var deleted:Boolean=false;
			try
			{
				var _statement:SQLStatement=new SQLStatement();
				var expenseData:Array=new Array();
				_statement.sqlConnection=_sqlConnection;
				_statement.text=ExpenseSQLScript.SELECT_EXPENSE_PAYEE;
				_statement.parameters[":payeeid"]=obj.pm_id;
				_statement.execute();
				expenseData=_statement.getResult().data;
				if (expenseData == null)
				{
					_statement=new SQLStatement();
					_statement.sqlConnection=_sqlConnection;
					_statement.text=ExpenseSQLScript.DELETE_PAYEE;
					_statement.parameters[":pm_payeeid"]=obj.pm_id;
					_statement.execute();
					deleted=true;
				}
				refreshPayee(_sqlConnection);
			}
			catch (sqlError:SQLError)
			{
				//Alert.show(sqlError.details);
			}
			return deleted;
		}
		public function deleteRecurringExpense(obj:Object):int
		{
			var _statement:SQLStatement=new SQLStatement();
			var connection:SQLConnection=CommonData.getConnection();
			try
			{
				_statement.sqlConnection=connection;
				_statement.text=ExpenseSQLScript.DELETE_RECURRING_EXPENSE;
				_statement.parameters[":rm_id"]=obj.rm_id;
				_statement.execute();
				refreshRecurringExpense(connection);
				connection.close();
				PMLogger.log("EXPENSE", "DELETE RECURRING EXPENSE", obj);
			}
			catch (sqlError:SQLError)
			{
				trace("Expense :deleteRecurringExpense : " + obj.rm_id + " " + sqlError.details);
				return 1;
			}
			return 0;
		}
		public function getCategoryDetailsByName(name:String):Object
		{
			var categoryObj:Object=new Object();
			for each (categoryObj in ExpenseDataCache.allCategoryData)
			{
				if (categoryObj.cm_categoryname == name)
					return categoryObj;
			}
			return categoryObj;
		}
		public function getCategoryDetailsForCatId(categoryId:int):String
		{
			var catObj:Object=new Object();
			for each (catObj in ExpenseDataCache.allCategoryData)
			{
				if (catObj.cm_categoryid == categoryId)
					return catObj.cm_categoryname;
			}
			return "";
		}
		public function getCategoryIdByName(name:String):int
		{
			var categoryObj:Object=new Object();
			for each (categoryObj in ExpenseDataCache.allCategoryData)
			{
				if (categoryObj.cm_categoryname == name)
					return categoryObj.cm_categoryid;
			}
			return 0;
		}
		public function getCategoryNameForCatId(categoryId:int):String
		{
			var catObj:Object=new Object();
			for each (catObj in ExpenseDataCache.allCategoryData)
			{
				if (catObj.cm_categoryid == categoryId)
					return catObj.cm_categoryname;
			}
			return "";
		}
		public function getExpenseForPayee(id:int, _sqlConnection:SQLConnection):Array
		{
			var expenseDetails:Array=new Array();
			try
			{
				var _statement:SQLStatement=new SQLStatement();
				_statement.sqlConnection=_sqlConnection;
				_statement.text=ExpenseSQLScript.SELECT_EXPENSE_PAYEE;
				_statement.parameters[":payeeid"]=id;
				_statement.execute();
				expenseDetails=_statement.getResult().data
			}
			catch (sqlError:SQLError)
			{
				//Alert.show(sqlError.details);
			}
			return expenseDetails;
		}
		private function generateRawBudgetData():void
		{
			var array:Array=new Array();
			for each (var obj:Object in ExpenseDataCache.budgetRawData)
			{
			}
		}
		public function getExpenseSummaryData(year:int):Array
		{
			var obj:Object=new Object();
			var arr:Array=new Array();
			for each (obj in ExpenseDataCache.expenseSummaryData)
			{
				if (obj.em_year == year)
					arr.push(obj)
			}
			return arr;
		}
		public function getPayeeDetailsByName(payeeName:String):Object
		{
			var payeeObj:Object=new Object();
			for each (payeeObj in ExpenseDataCache.payeeData)
			{
				if (payeeObj.pm_payeeName == payeeName)
					return payeeObj;
			}
			return payeeObj;
		}
		public function getPayeeIdByName(payeeName:String):int
		{
			var payeeObj:Object=new Object();
			for each (payeeObj in ExpenseDataCache.payeeData)
			{
				if (payeeObj.pm_payeeName == payeeName)
					return payeeObj.pm_id;
			}
			return 0;
		}
		public function getTransferAccountName(expenseid:int, accountid:int, _sqlConnection:SQLConnection):String
		{
			var _statement:SQLStatement=new SQLStatement();
			try
			{
				_statement.sqlConnection=_sqlConnection;
				_statement.text=ExpenseSQLScript.SELECT_TRANSFER_ACCOUNTNAME;
				_statement.parameters[":accountid"]=accountid;
				_statement.parameters[":expenseid"]=expenseid;
				_statement.execute();
				var accountNameList:ArrayCollection=new ArrayCollection(_statement.getResult().data);
				if (accountNameList.length > 0)
				{
					var accountName:String=accountNameList.getItemAt(0).am_accountname;
					return accountName;
				}
			}
			catch (sqlError:SQLError)
			{
				//Alert.show("can not update : " + sqlError.details);
			}
			return "";
		}
		public function getValidExpenseRecurssions():Array
		{
			var reObj:Object=new Object();
			var reArray:Array=new Array();
			for each (reObj in ExpenseDataCache.recurringExpenseData)
			{
				if (reObj.rm_status == 1)
					reArray.push(reObj);
			}
			return reArray;
		}
		public function loadData():void
		{
			var _sqlConnection:SQLConnection=CommonData.getConnection();
			//_sqlConnection.open();
			var _statement:SQLStatement=new SQLStatement();
			refreshExpenseData(_sqlConnection);
			//refreshBudgetData(_sqlConnection);
			refreshPayee(_sqlConnection);
			refreshCategory(_sqlConnection);
			refreshMasterCategory(_sqlConnection);
			refreshAccountSummary(_sqlConnection);
			refreshRecurringExpense(_sqlConnection);
			refreshYears(_sqlConnection);
			_sqlConnection.close();
			refreshDataGrid(null);
			refreshBudgetDataCache(null);
			flash.system.System.gc();
			flash.system.System.gc();
		}
		public function refreshExpenseDataCache(event:Event):void
		{
			trace("Expense : refreshExpenseCache : " + new Date().toTimeString() + " : Refresh Started");
			var connection:SQLConnection=CommonData.getConnection();
			refreshExpenseData(connection);
			refreshAccountSummary(connection);
			connection.close();
			refreshDataGrid(event);
			refreshBudgetDataCache(null);
			trace("Expense : refreshExpenseCache : " + new Date().toTimeString() + " : Refresh Ended");
			dispatchEvent(new Event("ExpenseDataCacheChanged"));
		}
		public function refreshBudgetDataCache(event:Event):void
		{
			trace("Budget : refreshBudgetCache : " + new Date().toTimeString() + " : Refresh Started");
			var connection:SQLConnection=CommonData.getConnection();
			refreshBudgetData(connection);
			var array1:Array=getBudgetPivotData();
			ExpenseDataCache.budgetFlatData.source=new Array();
			ExpenseDataCache.budgetFlatData.source=array1;
			//ExpenseDataCache.budgetFlatData=new ArrayCollection(flattenBudgetArray(array1));
			connection.close();
			trace("Budget : refreshBudgetCache : " + new Date().toTimeString() + " : Refresh Ended");
			dispatchEvent(new Event("BudgetDataCacheChanged"));
		}
		public function refreshPayee(_sqlConnection:SQLConnection):void
		{
			var _statement:SQLStatement=new SQLStatement();
			_statement.sqlConnection=_sqlConnection;
			_statement.text="select * from payeemanager where pm_userid  in (select userid from selecteduser)";
			//_statement.parameters[":USERID"]=AdminDataCache.ACCOUNT_IN_USE;
			_statement.execute();
			ExpenseDataCache.payeeData=new ArrayCollection(_statement.getResult().data);
		}
		public function updateCategory(obj:Object, _sqlConnection:SQLConnection):int
		{
			try
			{
				var _statement:SQLStatement=new SQLStatement();
				_statement.sqlConnection=_sqlConnection;
				_statement.text=ExpenseSQLScript.UPDATE_CATEGORY;
				_statement.parameters[":cm_categoryname"]=obj.categoryName;
				_statement.parameters[":cm_expensetype"]=obj.expensetype;
				_statement.parameters[":cm_categorydesc"]=obj.description;
				_statement.parameters[":cm_categoryid"]=obj.categoryId;
				_statement.parameters[":cm_mastercategory"]=obj.mastercategory;
				_statement.execute();
				loadData();
			}
			catch (sqlError:SQLError)
			{
				Alert.show(sqlError.details);
			}
			return 1;
		}
		public function updateDependentExpense(obj:Object, expenseId:Number, conn:SQLConnection):void
		{
			var _statement:SQLStatement=new SQLStatement();
			_statement.sqlConnection=conn;
			_statement.text=ExpenseSQLScript.UPDATE_EXPENSE_RECORD;
			_statement.parameters[":category"]=obj.category;
			_statement.parameters[":amount"]=obj.amount;
			_statement.parameters[":payee"]=obj.payee;
			_statement.parameters[":memo"]=obj.memo;
			_statement.parameters[":account"]=obj.account;
			_statement.parameters[":type"]=obj.type;
			_statement.parameters[":date"]=obj.date;
			_statement.parameters[":expenseid"]=expenseId;
			_statement.parameters[":exrate"]=obj.exRate;
			_statement.execute();
			//refreshExpenseData(conn);
			dispatchEvent(new Event("ExpenseTransactionUpdated"));
		}
		public function updateDependentTransferRecord(obj:Object, randomId:Number, conn:SQLConnection):int
		{
			try
			{
				deleteDependentExpense(randomId, true, conn, obj);
				addDependentTransferRecord(obj, randomId, conn, true);
			}
			catch (sqlError:SQLError)
			{
				Alert.show(sqlError.details);
				return 1;
			}
			return 0;
		}
		public function updateExpense(obj:Object, expenseId:Number):void
		{
			var sqlConnection:SQLConnection=CommonData.getConnection();
			var _statement:SQLStatement=new SQLStatement();
			_statement.sqlConnection=sqlConnection;
			_statement.text=ExpenseSQLScript.UPDATE_EXPENSE_RECORD;
			_statement.parameters[":category"]=obj.category;
			_statement.parameters[":amount"]=obj.amount;
			_statement.parameters[":payee"]=obj.payee;
			_statement.parameters[":memo"]=obj.memo;
			_statement.parameters[":account"]=obj.account;
			_statement.parameters[":type"]=obj.type;
			_statement.parameters[":date"]=obj.date;
			_statement.parameters[":expenseid"]=expenseId;
			_statement.parameters[":exrate"]=obj.exRate;
			_statement.execute();
			//refreshExpenseData(sqlConnection);
			sqlConnection.close();
			PMLogger.log("EXPENSE", "EXPENSE UPDATED", obj);
			dispatchEvent(new Event("ExpenseTransactionUpdated"));
		}
		public function updateMasterCategory(fieldName:String, value:Object, masterid:Number):void
		{
			var sqlConnection:SQLConnection=CommonData.getConnection();
			var _statement:SQLStatement=new SQLStatement();
			try
			{
				_statement.sqlConnection=sqlConnection;
				var UPDATE_MASTER_CATEGORY_FIELD:String="UPDATE mastercategory SET " + fieldName + " = :VALUE WHERE mc_id=:mc_id";
				_statement.text=UPDATE_MASTER_CATEGORY_FIELD;
				//_statement.parameters[":FIELDNAME"]=fieldName;
				_statement.parameters[":VALUE"]=value;
				_statement.parameters[":mc_id"]=masterid;
				_statement.execute();
				//refreshExpenseData(sqlConnection);
				_statement=null;
				PMLogger.log("EXPENSE", "EXPENSE UPDATED", value);
				//dispatchEvent(new Event("ExpenseTransactionUpdated"));
				refreshMasterCategory(sqlConnection);
				sqlConnection.close();
			}
			catch (e:Error)
			{
				trace(e.message);
			}
		}
		public function updateExpenseField(fieldName:String, value:Object, expenseId:Number):void
		{
			var sqlConnection:SQLConnection=CommonData.getConnection();
			var _statement:SQLStatement=new SQLStatement();
			try
			{
				_statement.sqlConnection=sqlConnection;
				var UPDATE_EXPENSE_RECORD_FIELD:String="UPDATE EXPENSEMANAGER SET " + fieldName + " = :VALUE WHERE em_expenseid=:expenseid";
				_statement.text=UPDATE_EXPENSE_RECORD_FIELD;
				//_statement.parameters[":FIELDNAME"]=fieldName;
				_statement.parameters[":VALUE"]=value;
				_statement.parameters[":expenseid"]=expenseId;
				_statement.execute();
				//refreshExpenseData(sqlConnection);
				sqlConnection.close();
				_statement=null;
				//PMLogger.log("EXPENSE", "EXPENSE UPDATED", value);
				dispatchEvent(new Event("ExpenseTransactionUpdated"));
			}
			catch (e:Error)
			{
				trace(e.message);
			}
		}
		public function addNewBudgetValue(object:Object):void
		{
			var sqlConnection:SQLConnection=CommonData.getConnection();
			var _statement:SQLStatement=new SQLStatement();
			try
			{
				_statement.sqlConnection=sqlConnection;
				var UPDATE_EXPENSE_RECORD_FIELD:String="insert or replace into  budgetmanager" + " (bm_category,bm_year,bm_amount,bm_month,bm_userid) VALUEs" + " (:bm_category,:bm_year,:bm_amount,:bm_month,:bm_userid)";
				_statement.text=UPDATE_EXPENSE_RECORD_FIELD;
				//_statement.parameters[":FIELDNAME"]=fieldName;
				_statement.parameters[":bm_category"]=object.bm_category;
				_statement.parameters[":bm_year"]=object.bm_year;
				_statement.parameters[":bm_amount"]=object.bm_amount;
				_statement.parameters[":bm_month"]=object.bm_month;
				_statement.parameters[":bm_userid"]=AdminDataCache.ACCOUNT_IN_USE;
				_statement.execute();
				//refreshExpenseData(sqlConnection);
				sqlConnection.close();
				_statement=null;
				PMLogger.log("EXPENSE", "EXPENSE UPDATED ", object);
				dispatchEvent(new Event("BudgetTransactionAdded"));
			}
			catch (e:Error)
			{
				trace(e.message);
			}
		}
		public function addNewBudgetTransaction(objects:Array):void
		{
			var sqlConnection:SQLConnection=CommonData.getConnection();
			var _statement:SQLStatement=new SQLStatement();
			try
			{
				_statement.sqlConnection=sqlConnection;
				sqlConnection.begin();
				var UPDATE_EXPENSE_RECORD_FIELD:String="insert into  budgetmanager" + " (bm_category,bm_date,bm_amount,bm_userid,bm_budget_type,bm_year,bm_month) VALUEs" + " (:bm_category,:bm_date,:bm_amount,:bm_userid,:bm_type,0,0)";
				_statement.text=UPDATE_EXPENSE_RECORD_FIELD;
				for each (var object:Object in objects)
				{
					//_statement.parameters[":FIELDNAME"]=fieldName;
					_statement.parameters[":bm_category"]=object.category;
					_statement.parameters[":bm_date"]=object.startDate;
					_statement.parameters[":bm_amount"]=object.amount;
					_statement.parameters[":bm_type"]=object.type;
					_statement.parameters[":bm_userid"]=AdminDataCache.ACCOUNT_IN_USE;
					_statement.execute();
					PMLogger.log("EXPENSE", "EXPENSE UPDATED ", object);
				}
				//refreshExpenseData(sqlConnection);
				sqlConnection.commit();
				sqlConnection.close();
				_statement=null;
				PMLogger.log("EXPENSE", "EXPENSE UPDATED ", object);
				dispatchEvent(new Event("BudgetTransactionAdded"));
			}
			catch (e:Error)
			{
				trace(e.message);
				sqlConnection.rollback();
				sqlConnection.close();
			}
		}
		public function deleteBudgetTransaction(objects:Array):void
		{
			var sqlConnection:SQLConnection=CommonData.getConnection();
			var _statement:SQLStatement=new SQLStatement();
			try
			{
				_statement.sqlConnection=sqlConnection;
				var DELETE_BUDGET_RECORD_FIELD:String="delete from budgetmanager where bm_budgetid = :id";
				_statement.text=DELETE_BUDGET_RECORD_FIELD;
				sqlConnection.begin();
				for each (var budgetRecord:Object in objects)
				{
					//_statement.parameters[":FIELDNAME"]=fieldName;
					_statement.parameters[":id"]=budgetRecord.bm_budgetid;
					_statement.execute();
					PMLogger.log("BUDGET", "DELETED", budgetRecord);
				}
				//refreshExpenseData(sqlConnection);
				sqlConnection.commit();
				sqlConnection.close();
				_statement=null;
				dispatchEvent(new Event("BudgetTransactionAdded"));
			}
			catch (e:Error)
			{
				trace(e.message);
				Alert.show(e.message);
			}
		}
		public function updateBudgetValue(fieldName:String, value:Object, id:Number):void
		{
			var sqlConnection:SQLConnection=CommonData.getConnection();
			var _statement:SQLStatement=new SQLStatement();
			try
			{
				_statement.sqlConnection=sqlConnection;
				var UPDATE_EXPENSE_RECORD_FIELD:String="update  budgetmanager set " + fieldName + "=  :value where bm_budgetid= :id";
				_statement.text=UPDATE_EXPENSE_RECORD_FIELD;
				//_statement.parameters[":FIELDNAME"]=fieldName;
				//_statement.parameters[":fieldName"]=fieldName;
				_statement.parameters[":value"]=value;
				_statement.parameters[":id"]=id;
				_statement.execute();
				//refreshExpenseData(sqlConnection);
				sqlConnection.close();
				_statement=null;
				//PMLogger.log("Budget", "Budget UPDATED ", value);
				dispatchEvent(new Event("BudgetTransactionAdded"));
			}
			catch (e:Error)
			{
				trace(e.message);
			}
		}
		public function updateRecurringExpenseField(fieldName:String, value:Object, expenseId:Number):void
		{
			var sqlConnection:SQLConnection=CommonData.getConnection();
			var _statement:SQLStatement=new SQLStatement();
			try
			{
				_statement.sqlConnection=sqlConnection;
				var UPDATE_EXPENSE_RECORD_FIELD:String="UPDATE recurringexpensemanager  SET " + fieldName + " = :VALUE WHERE rm_id=:expenseid";
				_statement.text=UPDATE_EXPENSE_RECORD_FIELD;
				//_statement.parameters[":FIELDNAME"]=fieldName;
				_statement.parameters[":VALUE"]=value;
				_statement.parameters[":expenseid"]=expenseId;
				_statement.execute();
				//refreshExpenseData(sqlConnection);
				refreshRecurringExpense(sqlConnection);
				sqlConnection.close();
				_statement=null;
				PMLogger.log("EXPENSE", "EXPENSE UPDATED", value);
			}
			catch (e:Error)
			{
				trace(e.message);
			}
		}
		public function updatePayee(obj:Object, _sqlConnection:SQLConnection):int
		{
			try
			{
				var _statement:SQLStatement=new SQLStatement();
				_statement.sqlConnection=_sqlConnection;
				_statement.text=ExpenseSQLScript.UPDATE_PAYEE;
				_statement.parameters[":pm_payeeName"]=obj.payeeName;
				_statement.parameters[":pm_id"]=obj.payeeId;
				_statement.execute();
				loadData();
			}
			catch (sqlError:SQLError)
			{
				Alert.show(sqlError.details);
			}
			return 1;
		}
		public function updateRecurrsiveExpenseNextExecutionData(obj:Object, _sqlConnection:SQLConnection):void
		{
			var _statement:SQLStatement=new SQLStatement();
			try
			{
				_statement.sqlConnection=_sqlConnection;
				_statement.text=ExpenseSQLScript.UPDATE_RECURRING_EXPENSE_NEXT_EXECUTION_DATE;
				_statement.parameters[":rm_nextexecutiondate"]=obj.nextExecutionDate;
				_statement.parameters[":rm_last_executed_date"]=obj.lastExecutedDate;
				_statement.parameters[":rm_id"]=obj.rm_id;
				_statement.execute();
				refreshRecurringExpense(_sqlConnection);
			}
			catch (sqlError:SQLError)
			{
				//Alert.show("can not update : " + sqlError.details);
				_sqlConnection.rollback();
			}
		}
		public function updateRecurssiveExpenseInfo(obj:Object, _sqlConnection:SQLConnection):int
		{
			var _statement:SQLStatement=new SQLStatement();
			try
			{
				_statement.sqlConnection=_sqlConnection;
				_statement.parameters[":rm_recursiondate"]=obj.onDate;
				_statement.parameters[":rm_amount"]=obj.amount;
				_statement.parameters[":rm_fromaccount"]=obj.account;
				_statement.parameters[":rm_toaccount"]=obj.toAccount;
				_statement.parameters[":rm_fromdate"]=obj.startDate;
				_statement.parameters[":rm_todate"]=obj.endDate;
				_statement.parameters[":rm_type"]=obj.type;
				_statement.parameters[":rm_category"]=obj.category;
				_statement.parameters[":rm_memo"]=obj.memo;
				_statement.parameters[":rm_status"]=obj.status;
				_statement.parameters[":rm_id"]=obj.rm_id;
				_statement.parameters[":rm_nextexecutiondate"]=RecurssionInterface.getNextExecutionDate(obj.startDate, obj.endDate, obj.onDate);
				_statement.text=ExpenseSQLScript.UPDATE_RECURSSIVE_EXPENSE;
				_statement.execute();
				refreshRecurringExpense(_sqlConnection);
			}
			catch (sqlError:SQLError)
			{
				trace(sqlError.details);
				return 1;
			}
			return 0;
		}
		public function updateTransferRecord(obj:Object):int
		{
			var sqlConnection:SQLConnection=CommonData.getConnection();
			try
			{
				// Delete the record 
				sqlConnection.begin();
				deleteDependentExpense(obj.expenseid, true, sqlConnection, obj);
				// Add new record				
				_addTransferRecord(obj, sqlConnection);
				sqlConnection.commit();
				//refreshExpenseData(sqlConnection);
				dispatchEvent(new Event("ExpenseTransactionUpdated"));
			}
			catch (sqlError:SQLError)
			{
				sqlConnection.rollback();
				sqlConnection.close();
				//Alert.show(sqlError.details);
				return 1;
			}
			sqlConnection.close();
			return 0;
		}
		private function _addTransferRecord(obj:Object, conn:SQLConnection):int
		{
			try
			{
				//var conn : SQLConnection = CommonData.getConnection();
				var randomId:Number=new Number();
				var depositObj:Object=new Object();
				var withdrawalObj:Object=new Object();
				depositObj=ObjectUtil.copy(obj);
				depositObj.type=0;
				depositObj.exRate=adminDataModel.getCurrencyDetailsForAccount(depositObj.account).cu_rate;
				randomId=Math.round(Math.random() * 1000000000);
				addObjToData(depositObj, randomId, conn);
				withdrawalObj=ObjectUtil.copy(obj);
				withdrawalObj.type=1;
				withdrawalObj.account=withdrawalObj.transferAccount;
				withdrawalObj.userid=adminDataModel.getAccountForId(withdrawalObj.transferAccount).am_userid;
				withdrawalObj.exRate=adminDataModel.getCurrencyDetailsForAccount(withdrawalObj.transferAccount).cu_rate;
				addObjToData(withdrawalObj, randomId, conn);
			}
			catch (sqlError:SQLError)
			{
				//Alert.show(sqlError.details);
				return 1;
			}
			return 0;
		}
		private function addObjToData(obj:Object, expenseId:Number, conn:SQLConnection):void
		{
			var _statement:SQLStatement=new PMSqlStatement();
			_statement.sqlConnection=conn;
			_statement.text=ExpenseSQLScript.EXPENSE_ADD_RECORD;
			_statement.parameters[":account"]=obj.account;
			_statement.parameters[":amount"]=obj.amount;
			_statement.parameters[":category"]=obj.category;
			_statement.parameters[":payee"]=obj.payee;
			_statement.parameters[":memo"]=obj.memo;
			_statement.parameters[":type"]=obj.type;
			_statement.parameters[":date"]=obj.date;
			_statement.parameters[":expenseid"]=expenseId;
			_statement.parameters[":exrate"]=obj.exRate;
			_statement.parameters[":instru"]=obj.instru;
			_statement.parameters[":USERID"]=obj.userid;
			if (obj.istransferrecord == null)
				_statement.parameters[":istransferrecord"]=0;
			else
				_statement.parameters[":istransferrecord"]=obj.istransferrecord;
			_statement.execute();
			_statement=null;
		}
		private function flattenCategoryArray(categoryArray:Array):Array
		{
			var flatArray:Array=new Array();
			var catObj:Object=new Object()
			var monthObj:Object=new Object();
			monthObj.category="Total";
			monthObj.January=0;
			monthObj.Feburary=0;
			monthObj.March=0;
			monthObj.April=0;
			monthObj.May=0;
			monthObj.June=0;
			monthObj.July=0;
			monthObj.August=0;
			monthObj.September=0;
			monthObj.October=0;
			monthObj.November=0;
			monthObj.December=0;
			monthObj.Total=0;
			for each (catObj in categoryArray)
			{
				var tempObj:Object=new Object;
				tempObj.em_year=catObj.em_year;
				tempObj.cm_expensetype=catObj.cm_expensetype;
				tempObj["cm_expensetypeid"]=catObj.cm_expensetypeid;
				tempObj.cm_categoryid=catObj.cm_categoryid;
				tempObj.category=catObj.cm_categoryname;
				tempObj["cm_categoryid"]=catObj.cm_categoryid;
				tempObj.cm_mastercategory=catObj.cm_mastercategory;
				tempObj.mc_categoryname=catObj.mc_categoryname;
				tempObj["mc_id"]=catObj.mc_id;
				var i:int=0;
				for (i=1; i <= 13; i++)
				{
					switch (i)
					{
						case 1:
							tempObj.January=catObj.data.January;
							//    monthObj.January=catObj.data.January+monthObj.January;
							break;
						case 2:
							tempObj.Feburary=catObj.data.Feburary;
							//   monthObj.Feburary=catObj.data.Feburary+monthObj.Feburary;
							break;
						case 3:
							tempObj.March=catObj.data.March;
							//   monthObj.March=catObj.data.March+monthObj.March;
							break;
						case 4:
							tempObj.April=catObj.data.April;
							//    monthObj.April=catObj.data.April+monthObj.April;
							break;
						case 5:
							tempObj.May=catObj.data.May;
							//   monthObj.May=catObj.data.May+monthObj.May;
							break;
						case 6:
							tempObj.June=catObj.data.June;
							//	monthObj.June=catObj.data.June+monthObj.June;
							break;
						case 7:
							tempObj.July=catObj.data.July;
							//   monthObj.July=catObj.data.July+monthObj.July;;
							break;
						case 8:
							tempObj.August=catObj.data.August;
							//  monthObj.August=catObj.data.August;
							break;
						case 9:
							tempObj.September=catObj.data.September;
							//  monthObj.September=catObj.data.September++monthObj.September;;
							break;
						case 10:
							tempObj.October=catObj.data.October;
							// monthObj.October=catObj.data.October+monthObj.October;
							break;
						case 11:
							tempObj.November=catObj.data.November;
							// monthObj.November=catObj.data.November+monthObj.November;;
							break;
						case 12:
							tempObj.December=catObj.data.December;
							//monthObj.December=catObj.data.December+monthObj.December;
							break;
						case 13:
							tempObj.Total=catObj.data.catTotal;
							// monthObj.Total=catObj.data.catTotal++monthObj.Total;
							break;
					}
				}
				flatArray.push(tempObj);
			}
			//flatArray.push(monthObj);
			//trace (flatArray);
			return flatArray;
		}
		private function flattenBudgetArray(budgetArray:Array):Array
		{
			var flatArray:Array=new Array();
			var catObj:Object=new Object()
			var monthObj:Object=new Object();
			monthObj.category="Total";
			monthObj.January=0;
			monthObj.Feburary=0;
			monthObj.March=0;
			monthObj.April=0;
			monthObj.May=0;
			monthObj.June=0;
			monthObj.July=0;
			monthObj.August=0;
			monthObj.September=0;
			monthObj.October=0;
			monthObj.November=0;
			monthObj.December=0;
			monthObj.Total=0;
			for each (catObj in budgetArray)
			{
				var tempObj:Object=new Object;
				tempObj.em_year=catObj.bm_year;
				tempObj.bm_category=catObj.bm_category;
				tempObj.cm_expensetype=catObj.cm_expensetype;
				tempObj.cm_categoryname=catObj.cm_categoryname;
				catObj.em_year=catObj.bm_year;
				var match:Array=ArrayUtils.findMatchingItem(catObj, ExpenseDataCache.expenseSummaryData.source, ["cm_categoryid", "em_year"]);
				if (match.length == 0)
				{
					var dummy:Object=new Object();
					dummy.January=0;
					dummy.Feburary=0;
					dummy.March=0;
					dummy.April=0;
					dummy.May=0;
					dummy.June=0;
					dummy.July=0;
					dummy.August=0;
					dummy.September=0;
					dummy.October=0;
					dummy.November=0;
					dummy.December=0;
					dummy.Total=0;
					match.push(dummy);
				}
				else
				{
					//trace ( "aaaa");
				}
				tempObj.bm_userid=catObj.bm_userid;
				tempObj["cm_mastercategory"]=catObj.cm_mastercategory;
				tempObj["mc_categoryname"]=catObj.mc_categoryname;
				tempObj["mc_id"]=catObj.mc_id;
				var i:int=0;
				for (i=1; i <= 13; i++)
				{
					switch (i)
					{
						case 1:
							tempObj.January=catObj.data.January;
							tempObj.January_actual=match[0].January;
							break;
						case 2:
							tempObj.Feburary=catObj.data.Feburary;
							tempObj.Feburary_actual=match[0].Feburary;
							//   monthObj.Feburary=catObj.data.Feburary+monthObj.Feburary;
							break;
						case 3:
							tempObj.March=catObj.data.March;
							tempObj.March_actual=match[0].March;
							//   monthObj.March=catObj.data.March+monthObj.March;
							break;
						case 4:
							tempObj.April_actual=match[0].April;
							tempObj.April=catObj.data.April;
							//    monthObj.April=catObj.data.April+monthObj.April;
							break;
						case 5:
							tempObj.May_actual=match[0].May;
							tempObj.May=catObj.data.May;
							//   monthObj.May=catObj.data.May+monthObj.May;
							break;
						case 6:
							tempObj.June=catObj.data.June;
							tempObj.June_actual=match[0].June;
							//	monthObj.June=catObj.data.June+monthObj.June;
							break;
						case 7:
							tempObj.July=catObj.data.July;
							tempObj.July_actual=match[0].July;
							//   monthObj.July=catObj.data.July+monthObj.July;;
							break;
						case 8:
							tempObj.August_actual=match[0].August;
							tempObj.August=catObj.data.August;
							//  monthObj.August=catObj.data.August;
							break;
						case 9:
							tempObj.September=catObj.data.September;
							tempObj.September_actual=match[0].September;
							//  monthObj.September=catObj.data.September++monthObj.September;;
							break;
						case 10:
							tempObj.October=catObj.data.October;
							tempObj.October_actual=match[0].October;
							// monthObj.October=catObj.data.October+monthObj.October;
							break;
						case 11:
							tempObj.November=catObj.data.November;
							tempObj.November_actual=match[0].November;
							// monthObj.November=catObj.data.November+monthObj.November;;
							break;
						case 12:
							tempObj.December=catObj.data.December;
							tempObj.December_actual=match[0].December;
							//monthObj.December=catObj.data.December+monthObj.December;
							break;
						case 13:
							tempObj.Total=catObj.data.catTotal;
							tempObj.Total_actual=match[0].Total;
							// monthObj.Total=catObj.data.catTotal++monthObj.Total;
							break;
					}
				}
				flatArray.push(tempObj);
			}
			return flatArray;
		}
		private function getExpensePivotData():Array
		{
			var categoryArray:Array=new Array();
			var obj:Object=new Object();
			var len:Array=new Array();
			for each (obj in ExpenseDataCache.expenseData)
			{
				{
					len=ArrayUtils.findMatchingItem(obj, categoryArray, ["em_year", "cm_categoryname"]);
					var field:String="";
					switch (obj.em_month)
					{
						case "01":
							field="January";
							break;
						case "02":
							field="Feburary";
							break;
						case "03":
							field="March";
							break;
						case "04":
							field="April";
							break;
						case "05":
							field="May";
							break;
						case "06":
							field="June";
							break;
						case "07":
							field="July";
							break;
						case "08":
							field="August";
							break;
						case "09":
							field="September";
							break;
						case "10":
							field="October";
							break;
						case "11":
							field="November";
							break;
						case "12":
							field="December";
							break;
						case "13":
							field="Total";
							break;
					}
					if (len.length == 0) //no item found
					{
						var obj1:Object=new Object();
						var i:int=0;
						obj1["em_year"]=obj.em_year;
						obj1["cm_expensetype"]=CommonConstants.getExpenseTypeString(obj.cm_expensetype);
						obj1["cm_expensetypeid"]=obj.cm_expensetype;
						obj1["cm_categoryname"]=obj.cm_categoryname;
						obj1["cm_categoryid"]=obj.cm_categoryid;
						obj1["cm_mastercategory"]=obj.cm_mastercategory;
						obj1["mc_categoryname"]=obj.mc_categoryname;
						obj1["mc_id"]=obj.mc_id;
						obj1["data"]=new Object();
						obj1.data.January=0;
						obj1.data.Feburary=0
						obj1.data.March=0
						obj1.data.April=0
						obj1.data.May=0
						obj1.data.June=0
						obj1.data.July=0
						obj1.data.August=0
						obj1.data.September=0
						obj1.data.October=0
						obj1.data.November=0
						obj1.data.December=0
						obj1.data.catTotal=0
						var amount:int=0;
						if (obj.em_expensetype == CommonConstants.WITHDRAWAL)
						{
							amount=obj.em_amount * -1;
						}
						else
						{
							amount=obj.em_amount;
						}
						obj1["data"][field]=amount;
						obj1["data"]["catTotal"]=amount;
						categoryArray.push(obj1);
					}
					else
					{
						amount=0;
						if (obj.em_expensetype == CommonConstants.WITHDRAWAL)
							amount=obj.em_amount * -1;
						else
							amount=obj.em_amount;
						len[0]["data"][field]=len[0]["data"][field] + amount;
						len[0]["data"]["catTotal"]=len[0]["data"]["catTotal"] + amount;
					}
				}
			}
			categoryArray.sortOn(["em_year", "cm_expensetype", "cm_categoryname"]);
			return categoryArray;
		}
		private function getBudgetPivotData():Array
		{
			var categoryArray:Array=new Array();
			var obj:Object=new Object();
			var len:Array=new Array();
			var obj2:Object=new Object();
			for each (obj in ExpenseDataCache.allCategoryData)
			{
				for each (obj2 in CommonData.yearLabel.source)
				{
					var obj1:Object=new Object();
					obj1.bm_year=obj2;
					obj1.bm_userid=AdminDataCache.ACCOUNT_IN_USE;
					obj1.cm_categoryname=obj.cm_categoryname;
					obj1["cm_expensetype"]=obj.cm_expensetype;
					obj1.cm_categoryid=obj.cm_categoryid;
					obj1["bm_category"]=obj.cm_categoryid;
					obj1["cm_mastercategory"]=obj.cm_mastercategory;
					obj1["mc_categoryname"]=obj.mc_categoryname;
					obj1["mc_id"]=obj.mc_id;
					obj1["data"]=new Object();
					obj1["data"]["1"]=new Object();
					obj1["data"]["1"].budget=0;
					obj1["data"]["1"].actual=getFilteredExpeneseData(obj.cm_categoryid, 1, obj1.bm_year);
					obj1["data"]["2"]=new Object();
					obj1["data"]["2"].budget=0;
					obj1["data"]["2"].actual=getFilteredExpeneseData(obj.cm_categoryid, 2, obj1.bm_year);
					obj1["data"]["3"]=new Object();
					obj1["data"]["3"].budget=0;
					obj1["data"]["3"].actual=getFilteredExpeneseData(obj.cm_categoryid, 3, obj1.bm_year);
					obj1["data"]["4"]=new Object();
					obj1["data"]["4"].budget=0;
					obj1["data"]["4"].actual=getFilteredExpeneseData(obj.cm_categoryid, 4, obj1.bm_year);
					obj1["data"]["5"]=new Object();
					obj1["data"]["5"].budget=0;
					obj1["data"]["5"].actual=getFilteredExpeneseData(obj.cm_categoryid, 5, obj1.bm_year);
					obj1["data"]["6"]=new Object();
					obj1["data"]["6"].budget=0;
					obj1["data"]["6"].actual=getFilteredExpeneseData(obj.cm_categoryid, 6, obj1.bm_year);
					obj1["data"]["7"]=new Object();
					obj1["data"]["7"].budget=0;
					obj1["data"]["7"].actual=getFilteredExpeneseData(obj.cm_categoryid, 7, obj1.bm_year);
					obj1["data"]["8"]=new Object();
					obj1["data"]["8"].budget=0;
					obj1["data"]["8"].actual=getFilteredExpeneseData(obj.cm_categoryid, 8, obj1.bm_year);
					obj1["data"]["9"]=new Object();
					obj1["data"]["9"].budget=0;
					obj1["data"]["9"].actual=getFilteredExpeneseData(obj.cm_categoryid, 9, obj1.bm_year);
					obj1["data"]["10"]=new Object();
					obj1["data"]["10"].budget=0;
					obj1["data"]["10"].actual=getFilteredExpeneseData(obj.cm_categoryid, 10, obj1.bm_year);
					obj1["data"]["11"]=new Object();
					obj1["data"]["11"].budget=0;
					obj1["data"]["11"].actual=getFilteredExpeneseData(obj.cm_categoryid, 11, obj1.bm_year);
					obj1["data"]["12"]=new Object();
					obj1["data"]["12"].budget=0;
					obj1["data"]["12"].actual=getFilteredExpeneseData(obj.cm_categoryid, 12, obj1.bm_year);
					obj1["data"]["catTotal"]=0;
					categoryArray.push(obj1);
				}
			}
			for each (obj in ExpenseDataCache.budgetRawData)
			{
				{
					var amount:Number=0;
					amount=obj.bm_amount;
					if (obj.bm_budget_type == CommonConstants.WITHDRAWAL)
						amount=amount * -1;
					len=ArrayUtils.findMatchingItem(obj, categoryArray, ["bm_year", "bm_userid", "cm_categoryid"]);
					if (len.length != 0)
					{
						//this is curious as the field name is string "01" and "1" will not match and hence has to be coverted to int
						len[0]["data"][int(obj.bm_month)].budget=len[0]["data"][int(obj.bm_month)].budget + amount;
						len[0]["data"][int(obj.bm_month)].actual=getFilteredExpeneseData(obj.cm_categoryid, obj.bm_month, obj.bm_year);
						len[0]["data"]["catTotal"]=len[0]["data"]["catTotal"] + amount;
					}
					else
					{
						obj1=new Object();
						obj1.bm_year=obj2;
						obj1.bm_userid=AdminDataCache.ACCOUNT_IN_USE;
						obj1.cm_categoryname=obj.cm_categoryname;
						obj1["cm_expensetype"]=obj.cm_expensetype;
						obj1.cm_categoryid=obj.cm_categoryid;
						obj1["bm_category"]=obj.cm_categoryid;
						obj1["cm_mastercategory"]=obj.cm_mastercategory;
						obj1["mc_categoryname"]=obj.mc_categoryname;
						obj1["mc_id"]=obj.mc_id;
						obj1["data"]=new Object();
						obj1["data"]["1"]=new Object();
						obj1["data"]["1"].budget=0;
						obj1["data"]["1"].actual=getFilteredExpeneseData(obj.cm_categoryid, 1, obj1.bm_year);
						obj1["data"]["2"]=new Object();
						obj1["data"]["2"].budget=0;
						obj1["data"]["2"].actual=getFilteredExpeneseData(obj.cm_categoryid, 2, obj1.bm_year);
						obj1["data"]["3"]=new Object();
						obj1["data"]["3"].budget=0;
						obj1["data"]["3"].actual=getFilteredExpeneseData(obj.cm_categoryid, 3, obj1.bm_year);
						obj1["data"]["4"]=new Object();
						obj1["data"]["4"].budget=0;
						obj1["data"]["4"].actual=getFilteredExpeneseData(obj.cm_categoryid, 4, obj1.bm_year);
						obj1["data"]["5"]=new Object();
						obj1["data"]["5"].budget=0;
						obj1["data"]["5"].actual=getFilteredExpeneseData(obj.cm_categoryid, 5, obj1.bm_year);
						obj1["data"]["6"]=new Object();
						obj1["data"]["6"].budget=0;
						obj1["data"]["6"].actual=getFilteredExpeneseData(obj.cm_categoryid, 6, obj1.bm_year);
						obj1["data"]["7"]=new Object();
						obj1["data"]["7"].budget=0;
						obj1["data"]["7"].actual=getFilteredExpeneseData(obj.cm_categoryid, 7, obj1.bm_year);
						obj1["data"]["8"]=new Object();
						obj1["data"]["8"].budget=0;
						obj1["data"]["8"].actual=getFilteredExpeneseData(obj.cm_categoryid, 8, obj1.bm_year);
						obj1["data"]["9"]=new Object();
						obj1["data"]["9"].budget=0;
						obj1["data"]["9"].actual=getFilteredExpeneseData(obj.cm_categoryid, 9, obj1.bm_year);
						obj1["data"]["10"]=new Object();
						obj1["data"]["10"].budget=0;
						obj1["data"]["10"].actual=getFilteredExpeneseData(obj.cm_categoryid, 10, obj1.bm_year);
						obj1["data"]["11"]=new Object();
						obj1["data"]["11"].budget=0;
						obj1["data"]["11"].actual=getFilteredExpeneseData(obj.cm_categoryid, 11, obj1.bm_year);
						obj1["data"]["12"]=new Object();
						obj1["data"]["12"].budget=0;
						obj1["data"]["12"].actual=getFilteredExpeneseData(obj.cm_categoryid, 12, obj1.bm_year); //obj1["data"][field]=0;
						obj1["data"]["catTotal"]=0;
						obj1["data"][obj.bm_month].budget=amount;
						obj1["data"][obj.bm_month].actual=getFilteredExpeneseData(obj.cm_categoryid, obj.bm_month, obj.bm_year);
						obj1["data"]["catTotal"]=obj1["data"]["catTotal"] + amount;
						categoryArray.push(obj1);
					}
				}
			}
			categoryArray.sortOn(["bm_year", "cm_categoryname"]);
			return categoryArray;
		}
		private function loadDataOnEvent(event:Event):void
		{
			loadData();
			flash.system.System.gc();
			flash.system.System.gc();
			try
			{
				new LocalConnection().connect('foo');
				new LocalConnection().connect('foo');
			}
			catch (e:*)
			{
			}
		}
		private function matchAccount(account:int, array:ArrayCollection):Object
		{
			var obj1:Object=new Object();
			for each (obj1 in array)
			{
				if (obj1.em_accountid == account)
				{
					return obj1
				}
			}
			return null;
		}
		private function refreshAccountSummary(_sqlConnection:SQLConnection):void
		{
			var _statement:SQLStatement=new PMSqlStatement();
			trace("Expense : refreshExpenseSummary : " + new Date().toTimeString() + " : Refresh Started");
			_statement.sqlConnection=_sqlConnection;
			_statement.text=ExpenseSQLScript.ACCOUNT_SUMMARY;
			_statement.execute();
			var obj:Object=new Object();
			var obj1:Object=new Object();
			ExpenseDataCache.accountSummary.source=new Array(); //removeAll();
			for each (obj in _statement.getResult().data)
			{
				obj1=matchAccount(obj.em_accountid, ExpenseDataCache.accountSummary);
				if (obj1 == null)
				{
					if (obj.em_expensetype == 0)
					{
						obj.amount=obj.amount * -1;
					}
					ExpenseDataCache.accountSummary.addItem(obj);
				}
				else
				{
					if (obj.em_expensetype == 1)
						obj1.amount=obj1.amount + obj.amount;
					else
						obj1.amount=obj1.amount - obj.amount;
				}
			}
			_statement=null;
			trace("Expense : refreshExpenseSummary : " + new Date().toTimeString() + " : Refresh Started");
		}
		private function refreshCategory(_sqlConnection:SQLConnection):void
		{
			var _statement:SQLStatement=new SQLStatement();
			_statement.sqlConnection=_sqlConnection;
			_statement.text=AdminSQLScript.SELECT_CATEGORY_ALL;
			_statement.execute();
			ExpenseDataCache.allCategoryData=new ArrayCollection(_statement.getResult().data);
			_statement=new SQLStatement();
			_statement.sqlConnection=_sqlConnection;
			_statement.text="select * from categorymanager where cm_categorytype!=2";
			_statement.execute();
			ExpenseDataCache.expenseCategoryData.source=_statement.getResult().data;
			//ExpenseDataCache.categoryData.refresh();
			_statement=null;
		}
		private function refreshMasterCategory(_sqlConnection:SQLConnection):void
		{
			var _statement:SQLStatement=new SQLStatement();
			_statement.sqlConnection=_sqlConnection;
			_statement.text=AdminSQLScript.SELECT_MASTER_CATEGORY_ALL;
			_statement.execute();
			ExpenseDataCache.masterCategory=new ArrayCollection(_statement.getResult().data);
			//ExpenseDataCache.categoryData.refresh();
			_statement=null;
		}
		private function refreshDataGrid(e:Event):void
		{
			//Alert.show("dddd");
			var array1:Array=getExpensePivotData();
			ExpenseDataCache.expenseSummaryData.source=new Array();
			ExpenseDataCache.expenseSummaryData=new ArrayCollection(flattenCategoryArray(array1));
		}
		private function refreshBudgetData(_sqlConnection:SQLConnection):void
		{
			var _statement:SQLStatement=new SQLStatement();
			trace("Expense : refreshExpenseData " + new Date().toTimeString() + " : Before Refresh");
			_statement.sqlConnection=_sqlConnection;
			_statement.text=ExpenseSQLScript.SELECT_BUDGET_DATA;
			_statement.execute();
			ExpenseDataCache.budgetRawData.source=new Array();
			ExpenseDataCache.budgetRawData.source=_statement.getResult().data;
		}
		private function refreshExpenseData(_sqlConnection:SQLConnection):void
		{
			trace("Expense : refreshExpenseData " + new Date().toTimeString() + " : Before Refresh");
			var _statement:SQLStatement=new SQLStatement();
			_statement.sqlConnection=_sqlConnection;
			_statement.text=ExpenseSQLScript.SELECT_FILTERED_EXPENSES_ALL;
			_statement.execute();
			ExpenseDataCache.expenseData.source=_statement.getResult().data;
			for each (var obj_data:Object in ExpenseDataCache.expenseData)
				if (obj_data.em_expensetype == 1)
				{
					obj_data.cr_amount=obj_data.em_amount;
					obj_data.dr_amount=0;
				}
				else
				{
					obj_data.dr_amount=obj_data.em_amount;
					obj_data.cr_amount=0;
				}
			trace("Expense : refreshExpenseData " + new Date().toTimeString() + " : After Refresh");
		}
		public static function getFilteredExpeneseData(category:int, month:int, year:int):Number
		{
			var dataArray:Array=new Array();
			var amount:Number=0;
			for each (var obj:Object in ExpenseDataCache.expenseSummaryData)
			{
				if (obj.cm_categoryid == category && obj.em_year == year)
				{
					//dataArray.push(obj);
					amount=obj[monthStringValue[month - 1]];
				}
			}
			return Math.abs(amount);
		}
		public static function getFilteredYearlyExpeneseData(month:int, year:int):Number
		{
			var dataArray:Array=new Array();
			var amount:Number=0;
			for each (var obj:Object in ExpenseDataCache.expenseData)
			{
				if (obj.em_year == year)
				{
					//dataArray.push(obj);
					amount=amount + obj[month];
				}
			}
			return amount;
		}
		private function refreshRecurringExpense(_sqlConnection:SQLConnection):void
		{
			var _statement:SQLStatement=new PMSqlStatement();
			_statement.sqlConnection=_sqlConnection;
			_statement.text=ExpenseSQLScript.SELECT_RECURRING_EXPENSES_ALL;
			_statement.execute();
			ExpenseDataCache.recurringExpenseData.source=_statement.getResult().data;
		}
		private function refreshYears(connection:SQLConnection):void
		{
			var _statement:SQLStatement=new PMSqlStatement();
			_statement.sqlConnection=connection;
			_statement.text=ExpenseSQLScript.EXPENSE_YEARS;
			_statement.execute();
			ExpenseDataCache.yearData.source=_statement.getResult().data;
		}
	}
}